from datetime import datetime
from typing import Union, Optional
from sqlalchemy import update, func, text, CursorResult, delete, insert
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.sql.operators import eq
from sqlmodel import select, desc, col, asc
from sqlmodel.sql.expression import Select, SelectOfScalar, and_

from src.base.beans import PageResult
from src.domain.sys_user import SysUser, SysUserReq, SysUserResp
from src.domain.sys_user_role import SysUserRole
from src.utils.common_utils import hash_password
from src.base.constants import id_generator


# 直接使用异步客户端
# async def get_user_by_id(user_id: int):
#     result = await asyncDbClient.execute(select(SysUser).where(SysUser.user_id == user_id).where(SysUser.del_flag == False))
#     return result.scalar_one_or_none()

# async def get_user_by_id(user_id: int):
#     # 当你 return user 时，async with 块已经结束，session 被关闭，user 对象就脱离了 Session（detached）。
#     async with async_db_session() as session:
#         result = await session.execute(select(SysUser).where(SysUser.user_id == user_id).where(SysUser.del_flag == False))
#         result: SysUser = result.scalars().one_or_none()
#         return result.copy(deep=True) # 深拷贝,因为此方法

async def get_user_by_id(dbSession: AsyncSession, id: int):
    result = await dbSession.execute(select(SysUser).where(SysUser.user_id == id).where(SysUser.del_flag == False))
    # 使用 scalars() 获取查询的第一个对象 ；若不使用scalars()，则返回一个元组，包含查询的所有结果。
    # 比如 ：select(SysUser, SysDept).where(SysUser.dept_id == SysDept.dept_id)
    # 使用 scalars() 后，返回的结果为：SysUser 集合
    # 不使用 scalars() 后，返回的结果为：(SysUser,SysDept) 元组集合
    return result.scalars().one_or_none()


async def get_user_by_user_name(dbSession: AsyncSession, user_name: str) -> SysUser:
    result = await dbSession.execute(
        select(SysUser).where(SysUser.user_name == user_name).where(eq(SysUser.del_flag, False)))
    return result.scalar_one_or_none()


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysUserReq = None):
    stmt = stmt.where(SysUser.del_flag == False)
    if entity:
        if entity.user_id:
            stmt = stmt.where(SysUser.user_id == entity.user_id)
        if entity.user_name:
            stmt = stmt.where(col(SysUser.user_name).like(f"%{entity.user_name}%"))
        if entity.nick_name:
            stmt = stmt.where(col(SysUser.nick_name).like(f"%{entity.nick_name}%"))
        if entity.email:
            stmt = stmt.where(SysUser.email == entity.email)
        if entity.phone:
            stmt = stmt.where(SysUser.phone == entity.phone)
        if entity.sex:
            stmt = stmt.where(SysUser.sex == entity.sex)
        if entity.user_type:
            stmt = stmt.where(SysUser.user_type == entity.user_type)
        if entity.status is not None:
            stmt = stmt.where(SysUser.status == entity.status)
        if entity.dept_ids and len(entity.dept_ids) > 0:
            search_sql = [f"{id} MEMBER OF(dept_ids_arr)" for id in entity.dept_ids]
            stmt = stmt.where(text(f"( {' OR '.join(search_sql)} )"))
        if entity.post_ids and len(entity.post_ids) > 0:
            search_sql = [f"{id} MEMBER OF(post_ids_arr)" for id in entity.post_ids]
            stmt = stmt.where(text(f"( {' OR '.join(search_sql)} )"))
        if entity.create_time_range:
            start_time = datetime.strptime(entity.create_time_range[0], "%Y-%m-%d").date()
            end_time = datetime.strptime(entity.create_time_range[1], "%Y-%m-%d").date()
            stmt = stmt.where(
                and_(func.date(SysUser.create_time) >= start_time, func.date(SysUser.create_time) <= end_time))
    return stmt


async def get_user_list_by_condition(dbSession: AsyncSession, entity: Optional[SysUserReq] = None) -> list[SysUserResp]:
    if entity is None:
        entity = SysUserReq()
    stmt = select(SysUser)
    stmt = where_handle(stmt, entity)
    stmt = stmt.order_by(desc(SysUser.sort))
    users = await dbSession.execute(stmt)
    return [SysUserResp(**user.model_dump()) for user in list(users.scalars())]


async def get_user_page_by_condition(dbSession: AsyncSession, entity: Optional[SysUserReq] = None) -> PageResult[SysUserResp]:
    if entity is None:
        entity = SysUserReq()
    list_stmt = select(SysUser)
    list_stmt = where_handle(list_stmt, entity)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysUser.sort))
    list_stmt = list_stmt.offset(entity.pageSize * (entity.current - 1)).limit(entity.pageSize)
    result = await dbSession.execute(list_stmt)
    result = list(result.scalars().all())
    users = [SysUserResp(**user.model_dump()) for user in result]

    total_stmt = select(func.count(SysUser.user_id))
    total_stmt = where_handle(total_stmt, entity)
    total_result = await dbSession.execute(total_stmt)
    total = total_result.scalar_one_or_none()
    return PageResult(total=total, data=users, current=entity.current, pageSize=entity.pageSize)


async def insert_user(dbSession: AsyncSession, entity: SysUserReq)->int:
    entity.user_id = next(id_generator)
    entity.password = hash_password(entity.password if entity.password else "123456")
    insert_data = SysUser(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    result = await dbSession.execute(insert(SysUser).values(**insert_data))
    # entity.user_id = result.lastrowid # 插入成功后，获取自增主键
    if entity.role_ids and len(entity.role_ids) > 0:
        mappings = [SysUserRole(user_id=entity.user_id, role_id=role_id) for role_id in entity.role_ids]
        await delete_user_role_mapping_by_ids(dbSession, [entity.user_id])
        await insert_user_role_mapping(dbSession, mappings)
    return int(result.rowcount)


async def update_user(dbSession: AsyncSession, entity: SysUserReq) -> int:
    update_data = SysUser(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    result = await dbSession.execute(
        update(SysUser).values(**update_data).where(col(SysUser.user_id).__eq__(entity.user_id)))
    if entity.role_ids and len(entity.role_ids) > 0:
        mappings = [SysUserRole(user_id=entity.user_id, role_id=role_id) for role_id in entity.role_ids]
        await delete_user_role_mapping_by_ids(dbSession, [entity.user_id])
        await insert_user_role_mapping(dbSession, mappings)
    return int(result.rowcount)


async def delete_user(dbSession: AsyncSession, id: int) -> int:
    # stmt = update(SysUser).values({"del_flag": True}).where(col(SysUser.user_id).__eq__(id))
    stmt = delete(SysUser).where(col(SysUser.user_id).__eq__(id))
    result: CursorResult = await dbSession.execute(stmt)
    await delete_user_role_mapping_by_ids(dbSession, [id])
    return int(result.rowcount)


async def delete_users(dbSession: AsyncSession, ids: list[int]) -> int:
    # stmt = update(SysUser).values({"del_flag": True}).where(col(SysUser.user_id).in_(ids))
    stmt = delete(SysUser).where(col(SysUser.user_id).in_(ids))
    result = await dbSession.execute(stmt)
    await delete_user_role_mapping_by_ids(dbSession, ids)
    return int(result.rowcount)


async def get_user_role_mapping_list_by_ids(dbSession: AsyncSession, ids: list[int] = None) -> list[SysUserRole]:
    if ids is None or len(ids) == 0:
        result = await dbSession.execute(select(SysUserRole))
        return list(result.scalars().all())
    else:
        result = await dbSession.execute(select(SysUserRole).where(col(SysUserRole.user_id).in_(ids)))
        return list(result.scalars().all())


async def delete_user_role_mapping_by_ids(dbSession: AsyncSession, ids: list[int] = None) ->int:
    result = await dbSession.execute(delete(SysUserRole).where(col(SysUserRole.user_id).in_(ids)))
    return int(result.rowcount)


async def insert_user_role_mapping(dbSession: AsyncSession, userRoleMappings: list[SysUserRole] = None):
    dbSession.add_all(userRoleMappings)


# async def get_user_list_with_dept(dbSession: AsyncSession) -> list[SysUserResp]:
#     sql = text(
#         f"SELECT u.user_id as user_id,u.user_name as user_name,u.nick_name as nick_name,"
#         f"jt.dept_id as dept_id, d.dept_name as dept_name, d.parent_id as parent_id "
#         f"FROM sys_user u "
#         f"LEFT JOIN JSON_TABLE( u.dept_ids, '$[*]' COLUMNS (dept_id INT PATH '$') ) AS jt ON true "
#         f"LEFT JOIN sys_dept d ON jt.dept_id = d.dept_id AND d.del_Flag = 0 "
#         f"WHERE u.del_flag = 0 "
#         f"GROUP BY jt.dept_id,u.user_id order by jt.dept_id desc")
#     stmt = select(SysUser, SysDept).from_statement(sql)
#     result = await dbSession.execute(stmt)
#     result = result.all()
#     result = [SysUserResp(
#         **user.model_dump(), dept_id=dept.dept_id, dept_name=dept.dept_name,  dept_pid=dept.parent_id
#     ) for (user, dept) in result]
#     return result
